'''
测试mysql数据库，用于后续使用数据库
'''
import mysql.connector
from mysql.connector import Error
from datetime import datetime
import hashlib
from my_util import Logger, encode_base64


loger = Logger()

mysql_server_ip = 'localhost'
username = 'root'
password = 'abc12345'
database_name = 'test_mysql'
datatable_name = 'process_bot'  # 测试漂流瓶机器人数据表

def string_to_sha256(input_string):
    # 创建一个新的sha256 hash对象
    sha_signature = hashlib.sha256()
    # 更新你要哈希的数据（字符串需要被编码为字节）
    sha_signature.update(input_string.encode('utf-8'))
    # 获取哈希值的十六进制表示
    hex_dig = sha_signature.hexdigest()
    return hex_dig

def get_database_connection(host, user, password, database):
    '''
    连接到数据库
    :param host: 数据库服务ip地址
    :param user: 用户名（须知mysql数据库服务默认root）
    :param password: 用户密码
    :param database: 数据库名称
    :return: 数据库连接对象
    '''

    connection = None

    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )

    except mysql.connector.Error as err:
        loger.error(f"Failed to create table: {err}")

    finally:
        return connection


def create_database(host, user, password, database):
    '''
    创建数据库
    :param host: 数据库服务ip地址
    :param user: 用户名（须知mysql数据库服务默认root）
    :param password: 用户密码
    :param database: 数据库名称
    :return: None
    '''
    try:
        # 连接到MySQL服务器
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password
        )

        if connection.is_connected():
            # 创建一个游标对象
            cursor = connection.cursor()
            # 检查数据库是否存在
            cursor.execute("SHOW DATABASES LIKE %s", (database,))
            if cursor.rowcount < 1:
                while cursor.nextset():     # 处理所有结果集，否则会cursor.close游标会报错
                    loger.debug(cursor.fetchall())
                # 创建数据库
                sql_query = f"CREATE DATABASE {database}"
                cursor.execute(sql_query)
                loger.info(f"Database {database} created successfully")
            else:
                loger.warning(f"Database {database} already exists")

    except Error as e:
        loger.warning(f"Error while connecting to MySQL {e}")

    finally:
        # 关闭连接
        if cursor:
            while cursor.nextset():     # 处理所有结果集，否则会cursor.close游标会报错
                loger.debug(cursor.fetchall())
            cursor.close()
        if connection.is_connected():
            connection.close()

    return

def create_datatable(host, user, password, database, create_table_query):
    '''
    创建数据表
    :param host: 数据库服务ip地址
    :param user: 用户名（须知mysql数据库服务默认root）
    :param password: 用户密码
    :param database: 数据库名称
    :return: None
    '''

    try:
        # 连接到MySQL数据库
        connection = get_database_connection(
            host=host,
            user=user,
            password=password,
            database=database
        )

        if connection is not None:
            # 创建一个游标对象
            cursor = connection.cursor()

            # 执行SQL语句
            cursor.execute(create_table_query)
            loger.debug("Table created successfully")
        else:
            loger.info("Failed to get connection")

    except mysql.connector.Error as err:
        loger.warning(f"Failed to create table: {err}")

    finally:
        # 关闭连接
        if cursor:
            cursor.close()
        if connection.is_connected():
            connection.close()

    return


def insert_table_opt(text_data, table_name=datatable_name):
    '''
    插入数据表
    :param table_name: 表名
    :param text_data: 待处理数据文本
    :return:
    '''
    # 插入数据表
    insert_table_format = """
    INSERT INTO {} (get_data_time, process_data, process_data_sha256, processed_flag) 
    VALUES (NOW(), '{}', '{}', 0)
    """
    insert_query = insert_table_format.format(table_name, encode_base64(text_data), string_to_sha256(text_data))

    loger.debug(f"insert query: {insert_query}")

    try:
        # 创建一个游标对象
        cursor = bot_connection.cursor()
        # 执行SQL语句
        cursor.execute(insert_query)
        # 事务提交，否则数据表当中可能没有变化
        bot_connection.commit()

    except mysql.connector.Error as err:
        loger.error(f"Failed to create table: {err}")

    finally:
        # 关闭游标
        if cursor:
            cursor.close()

    return

def select_table_opt(select_time, max_rows, processed_flag=-1, table_name=datatable_name):
    '''
    插入数据表
    :param table_name: 表名
    :param select_time: 查询时间
    :processed_flag: 是否经过分析，如果是-1则不区分（默认-1）
    :param max_rows: 最大查询返回行数
    :return:
    '''
    if processed_flag == -1:
        # 插入数据表
        insert_table_format = """
        SELECT * FROM {} 
        WHERE get_data_time <= '{}' 
        ORDER BY id desc
        LIMIT {}
        """
        select_query = insert_table_format.format(table_name, select_time, max_rows)
    elif processed_flag == 1 or processed_flag == 0:
        # 插入数据表
        insert_table_format = """
        SELECT * FROM {} 
        WHERE get_data_time <= '{}' AND processed_flag = {}
        ORDER BY id desc
        LIMIT {}
        """
        select_query = insert_table_format.format(table_name, select_time, processed_flag, max_rows)
    else:
        loger.error(f"not support processed flag {processed_flag}")
        return []


    loger.debug(f"select query: {select_query}")

    result = []

    try:
        # 创建一个游标对象
        cursor = bot_connection.cursor()
        # 执行SQL语句
        cursor.execute(select_query)
        if cursor:
            result = cursor.fetchall()    # 处理所有结果集，否则会cursor.close游标会报错
            for res in result:
                loger.debug(res)

    except mysql.connector.Error as err:
        loger.error(f"Failed to select table: {err}")

    finally:
        # 关闭游标
        if cursor:
            while cursor.nextset():     # 处理所有结果集，否则会cursor.close游标会报错
                loger.debug(cursor.fetchall())
            cursor.close()

    return result

def update_table_opt(question, answer, table_name=datatable_name):
    '''
    更新数据表
    :param table_name: 表名
    :param question: 问题
    :param answer: 答案
    :return:
    '''
    # 更新数据表
    update_table_format = """
    UPDATE {} AS t1
    JOIN(
        SELECT MIN(id) AS min_id
        FROM {}
        WHERE process_data_sha256 = '{}' AND processed_flag = 0
    ) AS t2 ON t1.id = t2.min_id
    SET t1.result_data = '{}' , t1.processed_flag = 1, t1.result_time = NOW()
    """
    print(f"answer {answer}")
    update_query = update_table_format.format(table_name, table_name, string_to_sha256(question), encode_base64(answer))

    loger.debug(f"update query: {update_query}")

    try:
        # 创建一个游标对象
        cursor = bot_connection.cursor()
        # 执行SQL语句
        cursor.execute(update_query)
        # 事务提交，否则数据表当中可能没有变化
        bot_connection.commit()

    except mysql.connector.Error as err:
        loger.error(f"Failed to update table: {err}")

    finally:
        # 关闭游标
        if cursor:
            cursor.close()

    return

def delete_table_opt(table_name, max_size):
    '''
    删除表记录
    :param table_name: 表名
    :param max_size: 表中最大记录条数
    :return:
    '''
    # 删除数据表记录
    delete_table_format = """
    DELETE p1 FROM {} 
    p1 LEFT JOIN (  
        SELECT id FROM {} ORDER BY id DESC LIMIT {} 
    ) p2 ON p1.id = p2.id  
    WHERE p2.id IS NULL;
    """
    delete_query = delete_table_format.format(table_name, table_name, max_size)

    loger.debug(f"delete query: {delete_query}")

    try:
        # 创建一个游标对象
        cursor = bot_connection.cursor()
        # 执行SQL语句
        cursor.execute(delete_query)
        # 事务提交，否则数据表当中可能没有变化
        bot_connection.commit()

    except mysql.connector.Error as err:
        loger.error(f"Failed to delete table: {err}")

    finally:
        # 关闭游标
        if cursor:
            cursor.close()

    return

def bot_sql_init():
    '''
    机器人数据库初始化
    :return:
    '''

    # 创建漂流瓶机器人表的SQL语句
    # 其中使用BIGINT作为主键类型，用于避免大量的数据插入后可能导致的id用尽
    # 还有其他方法避免主键冲突：1、使用uuid；2、使用类uuid，将时间转字符串并加入随机值，可以用于查询，减少键值设置。
    # 比如，时间转换的类uuid，20240518 203133 ---> 20240518203133123，其中生成的类uuid后加入了'123'作为随机值
    # get_data_time是数据记录插入数据库的时间，可以用于约等于获取待处理数据的时间
    # result_time是经过处理的数据结果，有的需要处理的数据基本没有延迟，此可以不用
    # process_data是待处理的文本数据，以json格式的数值字符串保存，可以保存多个需要处理的数据(暂时不实现多数据)
    # processed_flag是用于待处理数据与结果之间有较大延迟的情况下，数据按照单条处理输出，更新flag
    # 注意：process_data_sha256是process_data经过sha256计算得到hash码，用于查询匹配
    create_table_format = """ 
    CREATE TABLE IF NOT EXISTS {} (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,  
        get_data_time DATETIME NOT NULL,
        result_time DATETIME NULL,
        process_data TEXT NULL,
        process_data_sha256 CHAR(64) NULL,
        result_data TEXT NULL,
        processed_flag TINYINT(1) DEFAULT 0 NOT NULL
    )  
    """

    create_table_query = create_table_format.format(datatable_name) #创建漂流瓶机器人数据表

    loger.debug(f"create table query {create_table_query}")

    # 创建数据库
    create_database(mysql_server_ip, username, password, database_name)
    # 创建数据表
    create_datatable(mysql_server_ip, username, password, database_name, create_table_query)
    # 连接数据库
    global bot_connection
    bot_connection = get_database_connection(mysql_server_ip, username, password, database_name)


def bot_sql_quit():
    '''
    退出数据库
    :return:
    '''
    loger.info("ready to quit bot mysql connector ...")
    if bot_connection.is_connected():
        bot_connection.close()
    loger.info("quit bot mysql connector ok!")